/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.dal;

import edu.uoc.tdp.entity.Resource;
import edu.uoc.tdp.customException.EIssueException;
import edu.uoc.tdp.i18n.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
  
/**
 * @author J2RS / Jordi Sanchez
 */
public class ManagementResource {

   private ManagementDb mngBD = null;
   private Connection conexion = null;

    public ManagementResource() throws EIssueException{
        try {
            mngBD = new ManagementDb();
            mngBD.connectDB();
            conexion = mngBD.getConnection();
        } catch (edu.uoc.tdp.common.EIssueException ex) {
            Logger.getLogger(ManagementResource.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ManagementResource(ManagementDb mngDb) {
        mngBD = mngDb;
        conexion = mngDb.getConnection();
    }

    /**
     * Mètode que obte la lista de espais - taula AllResources
     * @return Lista de espais
     */
    public List<Resource> GetAllResources() throws EIssueException{
        List<Resource> listResources = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid, resourcedescription, amount, price_hour, price_day, creation_date, creation_user, inactivation_date, inactivation_user, record_date  FROM all_resources";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getDate(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10) ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listResources;
    }

    /**
     * Mètode que obte la lista de Resource - taula all_resources
     * @return Lista de espais
     */
    public List<Resource> GetAllResourcesTable() throws EIssueException{
        List<Resource> listResource = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid, resourcedescription, amount, price_hour, price_day FROM all_resources";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listResource.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listResource;
    }
    
    
    /**
     * Mètode que obte un espai - taula AllResources
     * @return Lista de espai
     */
    public List<Resource> GetOneResource(int resourceid) throws EIssueException {
        List<Resource> listResources = new ArrayList();
        
        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid, resourcedescription, amount, price_hour, price_day, creation_date, creation_user, inactivation_date, inactivation_user, record_date FROM all_resources WHERE resourceid=" + String.valueOf(resourceid);
            //PreparedStatement command = this.mngBD.prepareCommand(select);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                                  
                listResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDouble(4), rs.getDouble(5), rs.getDate(6), rs.getString(7), rs.getDate(8), rs.getString(9), rs.getDate(10) ));
                
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listResources;
    }
    

    /**
     * Mètode comprova si existeix el registre i que obte un boolean - taula all_resources
     * @return boolean
     */
    public boolean CheckExistsResource(int resourceid) throws EIssueException {
        
        boolean Result=false;
        try {           
                        
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid FROM all_resources WHERE resourceid=" + String.valueOf(resourceid);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                 Result=true;        
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return Result;
    }
    
   
    /**
     * Mètode que obte una llista de Espais, filtrats per Descricio - taula Spaces
     * @return Lista de espais
     */
    public List<Resource> GetResource_FindByDesc(String Desc) throws EIssueException {
        List<Resource> listResources = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid, resourcedescription, amount, price_hour, price_day FROM all_resources WHERE resourcedescription like '%" + Desc + "%'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listResources;
    }  

    /**
     * Mètode que obte una llista de Espais, filtrats per Codi Postal - taula Spaces
     * @return Lista de espais
     */
    public List<Resource> GetResource_FindByResourceid(Integer resourceid) throws EIssueException {
        List<Resource> listResources = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT resourceid, resourcedescription, amount, price_hour, price_day FROM spaces WHERE resourceid='" + String.valueOf(resourceid) + "'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listResources;
    }  
    
    
    /**
     * Mètode que guarda dades (Update) un espai - taula AllResources
     * @return boolea
     */    
    public boolean SaveResource(Resource Res) throws EIssueException{
        boolean PROCESS_STATE = false;
        try {
            String insertQuery = "INSERT INTO all_resources(resourceid, resourcedescription, amount, price_hour, price_day, creation_date, creation_user, record_date)   VALUES (?, ?, ?, ?, ?,now(), ?,now())";
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setInt(1, Res.getResourceId());
            command.setString(2, Res.getResourceDescription());
            command.setInt(3, Res.getAmount());
            command.setDouble(4, Res.getPriceHour());
            command.setDouble(5, Res.getPriceDay());
            command.setString(6, Res.getCreationUser());            
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return PROCESS_STATE;
    }

    /**
     * Mètode que fa Update de un espai - taula AllResources
     * @return boolea
     */    
    public boolean SaveResourceChanges(Resource Space) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "UPDATE all_resources   SET resourcedescription=?, amount=?, price_hour=?, price_day=? WHERE resourceid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);                       
            command.setString(1, Space.getResourceDescription());
            command.setInt(2, Space.getAmount());
            command.setDouble(3, Space.getPriceHour());
            command.setDouble(4, Space.getPriceDay());
            command.setInt(5, Space.getResourceId());
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    /**
     * Mètode que fa dona de baixa/desactiva un espai - taula AllResources
     * @return boolea
     */     
    public boolean SaveInactivationResource(int resourceId, String userName) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "UPDATE all_resources SET inactivation_date=now(), inactivation_user=?  WHERE resourceId=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);
            command.setString(1, userName);
            command.setInt(2, resourceId);
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }


}
